SET NOCOUNT ON
GO
--USE [ManageSchool]
--GO
CREATE PROCEDURE [dbo].[pr_tblBehaviour_Insert]
	@PupilID char(10),
	@AwardAndPunish ntext,
	@AbsentCount int,
	@LastAbsent datetime,
	@MonthOfEval int,
	@Eval nvarchar(10),
	@TeacherComm ntext
AS
INSERT [dbo].[tblBehaviour]
(
	[PupilID],
	[AwardAndPunish],
	[AbsentCount],
	[LastAbsent],
	[MonthOfEval],
	[Eval],
	[TeacherComm]
)
VALUES
(
	@PupilID,
	@AwardAndPunish,
	@AbsentCount,
	@LastAbsent,
	@MonthOfEval,
	@Eval,
	@TeacherComm
)
GO

CREATE PROCEDURE [dbo].[pr_tblBehaviour_updateConduct]
	@PupilID char(10),	
	@MonthOfEval int,
	@Eval nvarchar(10)
AS
UPDATE [dbo].[tblBehaviour]
SET 	
	[Eval] = @Eval
WHERE
	[PupilID] = @PupilID
	AND [MonthOfEval] = @MonthOfEval

GO

CREATE PROCEDURE [dbo].[pr_tblBehaviour_Delete]
	@PupilID char(10),
	@MonthOfEval int
AS
DELETE FROM [dbo].[tblBehaviour]
WHERE
	[PupilID] = @PupilID
	AND [MonthOfEval] = @MonthOfEval
GO

CREATE PROCEDURE [dbo].[pr_tblBehaviour_DeleteWPupilIDLogic]
	@PupilID char(10)
AS
DELETE FROM [dbo].[tblBehaviour]
WHERE
	[PupilID] = @PupilID
GO

CREATE PROCEDURE [dbo].[pr_tblBehaviour_DeleteWMonthOfEvalLogic]
	@MonthOfEval int
AS
DELETE FROM [dbo].[tblBehaviour]
WHERE
	[MonthOfEval] = @MonthOfEval
GO

CREATE PROCEDURE [dbo].[pr_tblBehaviour_SelectAll]
AS
SELECT * FROM [dbo].[tblBehaviour]
ORDER BY 
	[PupilID] ASC
	, [MonthOfEval] ASC
GO

CREATE PROCEDURE [dbo].[pr_tblBehaviour_SelectAllWPupilIDLogic]
	@PupilID char(10)
AS
SELECT * FROM [dbo].[tblBehaviour]
WHERE
	[PupilID] = @PupilID
GO

CREATE PROCEDURE [dbo].[pr_SelectForCheckAttendance]
	@Class char(10)
AS
SELECT p.PupilID,FullName,datediff(dy,LastAbsent,getdate()) as LastAbsent,AbsentCount 
FROM tblBehaviour as b join tblPupil as p on b.PupilID=p.PupilID
WHERE
	p.ClassID=@Class
	AND b.MonthOfEval = Month(getdate())
GO

CREATE PROCEDURE pr_tblBehaviour_Absent
	@Pupil char(10)
AS
SELECT sum(AbsentCount) FROM tblBehaviour WHERE PupilID=@Pupil


GO

CREATE PROCEDURE [dbo].[pr_tblBehaviour_MarkAbsent]
	@Pupil char(10)
AS
DECLARE @d datetime
SELECT @d=LastAbsent from [dbo].[tblBehaviour] WHERE PupilID=@Pupil AND MonthOfEval=Month(getdate())
IF(datediff(dy,@d,getdate())>0)
BEGIN
	UPDATE [dbo].[tblBehaviour] 
	SET 	
		LastAbsent=getdate(),
		AbsentCount=AbsentCount+1
	WHERE 
		PupilID=@Pupil 
		AND MonthOfEval=Month(getdate())
END
GO
/*
CREATE TRIGGER MarkAbsent ON [dbo].[tblBehaviour] 
FOR  UPDATE
AS
DECLARE @p char(10),@m int, @a datetime
SELECT @p=PupilID,@m=MonthOfEval FROM updated
SELECT @a=LastAbsent FROM tblBehaviour WHERE PupilID=@p and MonthOfEval=@m
IF(@a=getdate())
	ROLLBACK
GO*/

CREATE PROCEDURE [dbo].[pr_tblPupil_loadClassList]
	@Class char(10)
AS
SELECT *,
Case tblPupil.Gender when '1' then 'Nam' when '0' then N'N?' END as Sex
FROM tblPupil
WHERE 	ClassID=@Class
GO

CREATE PROCEDURE pr_tblBehaviour_pupilConduct
	@Pupil char(10)
AS
SELECT Eval,MonthOfEval 
FROM tblBehaviour
WHERE	PupilID=@Pupil
GO

CREATE PROCEDURE [dbo].[pr_tblBehaviour_Reset]
AS
UPDATE [dbo].[tblBehaviour]
SET 
	[AwardAndPunish] = '',
	[AbsentCount] = 0,
	[LastAbsent] = '',
	[Eval] = '',
	[TeacherComm] = ''
GO

CREATE PROCEDURE [dbo].[pr_tblClass_Insert]
	@ID char(10),
	@Name char(10),
	@Grade int,
	@HomeTeacher char(10),
	@Type char(10)
AS
INSERT [dbo].[tblClass]
(
	[ID],
	[Name],
	[Grade],	
	[Type]
)
VALUES
(
	@ID,
	@Name,
	@Grade,
	@Type
)
GO

CREATE PROCEDURE [dbo].[pr_tblClass_Update]
	@ID char(10),
	@Name char(10),
	@Grade int,
	@Type char(10)
AS
UPDATE [dbo].[tblClass]
SET 
	[Name] = @Name,
	[Grade] = @Grade,
	[Type] = @Type
WHERE
	[ID] = @ID
GO


CREATE PROCEDURE [dbo].[pr_tblClass_Delete]
	@ID char(10)
AS
DELETE FROM [dbo].[tblClass]
WHERE
	[ID] = @ID
GO

CREATE PROCEDURE [dbo].[pr_tblClass_SelectAll]
AS
SELECT * FROM [dbo].[tblClass]
ORDER BY 
	[ID] ASC
GO

CREATE PROCEDURE [dbo].[pr_tblClass_SelectName]
AS
SELECT ID,rtrim(Grade)+rtrim(Name) as ClassName FROM tblClass WHERE Grade<13
ORDER BY 
	[ID] ASC
GO

CREATE PROCEDURE [dbo].[pr_tblClass_Upgrade]
	@Class char(10)
AS
UPDATE tblClass SET Grade=Grade+1
WHERE [ID]=@Class
GO

CREATE PROCEDURE pr_tblClass_SelectByType
	@Class char(10)
AS
SELECT * FROM tblClass WHERE [ID] like @Class+'%'
GO

CREATE PROCEDURE pr_tblClass_SelectCountPupil
	@Class char(10)
AS
SELECT count(PupilID) FROM tblPupil WHERE classID = @Class
GO

CREATE PROCEDURE pr_tblMark_setMarkConstant
AS
UPDATE tblMark SET Status=1
GO

CREATE PROCEDURE pr_tblMark_SelectMark
	@Pupil char(10),
	@Sub char(10)
AS
SELECT * FROM tblMark 
WHERE 	Pupil = @Pupil 
	AND Subject = @Sub 
ORDER BY MarkType
GO

CREATE PROCEDURE [dbo].[pr_tblMark_avgMark]
	@Pupil char(10),
	@Subject char(5)
AS
	SELECT round(sum(MarkValue*Ratio)/sum(Ratio),2) 
	FROM [dbo].[tblMark]
	WHERE Pupil=@Pupil and Subject=@Subject
GO

CREATE PROCEDURE [dbo].[pr_tblMark_Insert]	
	@MarkValue float(53),
	@MarkType nvarchar(20),
	@Ratio int,
	@Pupil char(10),
	@Subject nvarchar(20)
AS
INSERT [dbo].[tblMark]
(	
	[MarkValue],
	[MarkType],
	[Ratio],
	[Pupil],
	[Subject],
	[DateOfMark],
	[Status]
)
VALUES
(
	@MarkValue,
	@MarkType,
	@Ratio,
	@Pupil,
	@Subject,
	getdate(),
	0
)
GO

CREATE PROCEDURE [dbo].[pr_tblMark_Update]
	@ID int,
	@MarkValue float(53)
AS
DECLARE @Status bit
SELECT @Status=Status FROM tblMark WHERE ID=@ID
IF(@Status=0)
BEGIN
UPDATE [dbo].[tblMark]
SET 
	[MarkValue] = @MarkValue	
WHERE
	[ID] = @ID
END
GO

CREATE PROCEDURE [dbo].[pr_tblMark_UpdateAllWPupilLogic]
	@Pupil char(10),
	@PupilOld char(10)
AS
UPDATE [dbo].[tblMark]
SET
	[Pupil] = @Pupil
WHERE
	[Pupil] = @PupilOld
GO

CREATE PROCEDURE [dbo].[pr_tblMark_UpdateAllWSubjectLogic]
	@Subject nvarchar(20),
	@SubjectOld nvarchar(20)
AS
UPDATE [dbo].[tblMark]
SET
	[Subject] = @Subject
WHERE
	[Subject] = @SubjectOld
GO

CREATE PROCEDURE [dbo].[pr_tblMark_Delete]
	@ID int
AS
DELETE FROM [dbo].[tblMark]
WHERE
	[ID] = @ID
GO

CREATE PROCEDURE [dbo].[pr_tblMark_SelectAll]
AS
SELECT * FROM [dbo].[tblMark]
ORDER BY 
	[ID] ASC
GO

CREATE PROCEDURE [dbo].[pr_tblMark_SelectAllWPupilLogic]
	@Pupil char(10)
AS
SELECT * FROM [dbo].[tblMark]
WHERE
	[Pupil] = @Pupil
GO


CREATE PROCEDURE [dbo].[pr_tblMark_SelectAllWSubjectLogic]
	@Subject nvarchar(20)
AS
SELECT * FROM [dbo].[tblMark]
WHERE
	[Subject] = @Subject
GO



CREATE PROCEDURE [dbo].[pr_tblPupil_Insert]
	@PupilID char(10),
	@FullName nvarchar(50),
	@classID char(10),
	@Gender bit,
	@Birth datetime,
	@BirthPlace nvarchar(50),
	@NativePlace nvarchar(50),
	@Address nvarchar(50),
	@Phone varchar(50),
	@EthnicGroup nchar(10),
	@FatherName nvarchar(50),
	@FatherJob nvarchar(50),
	@FatherBirth int,
	@MotherName nvarchar(50),
	@MotherJob nvarchar(50),
	@MotherBirth int,
	@ParentEmail varchar(30),
	@EnterDate datetime,
	@ReleaseDate datetime,
	@Status nvarchar(50),
	@Picture varchar(50)
AS
INSERT [dbo].[tblPupil]
(
	[PupilID],
	[FullName],
	[classID],
	[Gender],
	[Birth],
	[BirthPlace],
	[NativePlace],
	[Address],
	[Phone],
	[EthnicGroup],
	[FatherName],
	[FatherJob],
	[FatherBirth],
	[MotherName],
	[MotherJob],
	[MotherBirth],
	[ParentEmail],	
	[EnterDate],
	[ReleaseDate],
	[Status],
	[Picture]
)
VALUES
(
	@PupilID,
	@FullName,
	@classID,
	@Gender,
	@Birth,
	@BirthPlace,
	@NativePlace,
	@Address,
	@Phone,
	@EthnicGroup,
	@FatherName,
	@FatherJob,
	@FatherBirth,
	@MotherName,
	@MotherJob,
	@MotherBirth,
	@ParentEmail,	
	@EnterDate,
	@ReleaseDate,
	@Status,
	@Picture
)
GO

CREATE PROCEDURE [dbo].[pr_tblPupil_Update]
	@PupilID char(10),
	@FullName nvarchar(50),
	@classID char(10),
	@Gender bit,
	@Birth datetime,
	@BirthPlace nvarchar(50),
	@NativePlace nvarchar(50),
	@Address nvarchar(50),
	@Phone varchar(50),
	@EthnicGroup nchar(10),
	@FatherName nvarchar(50),
	@FatherJob nvarchar(50),
	@FatherBirth int,
	@MotherName nvarchar(50),
	@MotherJob nvarchar(50),
	@MotherBirth int,
	@ParentEmail varchar(30),	
	@EnterDate datetime,
	@ReleaseDate datetime,
	@Status nvarchar(50),
	@Picture varchar(50)
AS
UPDATE [dbo].[tblPupil]
SET 
	[FullName] = @FullName,
	[classID] = @classID,
	[Gender] = @Gender,
	[Birth] = @Birth,
	[BirthPlace] = @BirthPlace,
	[NativePlace] = @NativePlace,
	[Address] = @Address,
	[Phone] = @Phone,
	[EthnicGroup] = @EthnicGroup,
	[FatherName] = @FatherName,
	[FatherJob] = @FatherJob,
	[FatherBirth] = @FatherBirth,
	[MotherName] = @MotherName,
	[MotherJob] = @MotherJob,
	[MotherBirth] = @MotherBirth,
	[ParentEmail] = @ParentEmail,	
	[EnterDate] = @EnterDate,
	[ReleaseDate] = @ReleaseDate,
	[Status] = @Status,
	[Picture] = @Picture
WHERE
	[PupilID] = @PupilID
GO

CREATE PROC pr_tblPupil_switchClass
	@Pupil char(10),
	@Class char(10)
AS
Update tblPupil set classID=@Class where PupilID=@Pupil

CREATE PROCEDURE [dbo].[pr_tblPupil_UpdateAllWclassIDLogic]
	@classID char(10),
	@classIDOld char(10)
AS
UPDATE [dbo].[tblPupil]
SET
	[classID] = @classID
WHERE
	[classID] = @classIDOld
GO

CREATE PROCEDURE pr_tblPupil_SelectByStatus
	@Status char(10)
AS
SELECT * FROM tblPupil WHERE Status = @Status
GO

CREATE PROCEDURE [dbo].[pr_tblPupil_Delete]
	@PupilID char(10)
AS
DELETE FROM [dbo].[tblPupil]
WHERE
	[PupilID] = @PupilID
GO

CREATE PROCEDURE [dbo].[pr_tblPupil_SelectAll]
	@PupilID char(10)
AS
SELECT * FROM [dbo].[tblPupil]
WHERE
	[PupilID] = @PupilID
ORDER BY 
	[PupilID] ASC
GO


CREATE PROCEDURE [dbo].[pr_tblPupil_SelectOne]
	@FullName nvarchar(50),
	@Gender bit,
	@Birth datetime,
	@NativePlace nvarchar(50),
	@Address nvarchar(50),
	@FatherName nvarchar(50),
	@FatherBirth int,
	@MotherName nvarchar(50),
	@MotherBirth int
AS
SELECT * FROM [dbo].[tblPupil]
WHERE
	[FullName] = @FullName and
	[Gender] = @Gender and
	[Birth] = @Birth and
	[NativePlace] = @NativePlace and
	[Address] = @Address and
	[FatherName] = @FatherName and
	[FatherBirth] = @FatherBirth and
	[MotherName] = @MotherName and
	[MotherBirth] = @MotherBirth
GO


CREATE PROCEDURE [dbo].[pr_tblPupil_SelectTop]
AS
SELECT MAX([PupilID]) FROM [dbo].[tblPupil]
Go  

CREATE PROCEDURE [dbo].[pr_tblPupil_SearchAll]
	@PupilID char(10),
	@FullName nvarchar(50),
	@classID char(10)
AS
SELECT * FROM [dbo].[tblPupil]
WHERE
	[PupilID] like '%'+@PupilID+'%' or
	[FullName] like '%'+@FullName+'%' and 
	[classID] like '%'+@classID+'%'
ORDER BY
	[PupilID] ASC
GO
---

CREATE PROCEDURE [dbo].[pr_tblPupil_SelectAllWclassIDLogic]
	@classID char(10)
AS
SELECT * FROM [dbo].[tblPupil]
WHERE
	[classID] = @classID
GO

CREATE PROCEDURE pr_tblPupil_SetStatus
	@PupilID char(10),
	@Status	nvarchar(50)
AS
UPDATE tblPupil SET Status=@Status
WHERE PupilID=@PupilID
GO

CREATE PROCEDURE [dbo].[pr_tblSubject_Insert]
	@SubjectName nvarchar(20)
AS
INSERT [dbo].[tblSubject]
(
	[SubjectName]
)
VALUES
(
	@SubjectName
)
GO

CREATE PROCEDURE [dbo].[pr_tblSubject_Delete]
	@SubjectName nvarchar(20)
AS
DELETE FROM [dbo].[tblSubject]
WHERE
	[SubjectName] = @SubjectName
GO

CREATE PROCEDURE [dbo].[pr_tblSubject_SelectAll]
AS
SELECT * FROM [dbo].[tblSubject]
ORDER BY 
	[SubjectName] ASC
GO

CREATE PROCEDURE [dbo].[pr_tblTaskAssign_Insert]
	@Class char(10),
	@Teacher char(10),
	@Subject nvarchar(20),
	@TimeSlot char(10)
AS
INSERT [dbo].[tblTaskAssign]
(
	[Class],
	[Teacher],
	[Subject],
	[TimeSlot]
)
VALUES
(
	@Class,
	@Teacher,
	@Subject,
	@TimeSlot
)
GO

CREATE PROCEDURE [dbo].[pr_tblTaskAssign_Update]
	@Class char(10),
	@Teacher char(10),
	@Subject nvarchar(20),
	@TimeSlot char(10)
AS
UPDATE [dbo].[tblTaskAssign]
SET 
	[TimeSlot] = @TimeSlot
WHERE
	[Class] = @Class
	AND [Teacher] = @Teacher
	AND [Subject] = @Subject
GO

CREATE PROCEDURE [dbo].[pr_tblTaskAssign_Delete]
	@Class char(10),
	@Teacher char(10),
	@Subject nvarchar(20)
AS
DELETE FROM [dbo].[tblTaskAssign]
WHERE
	[Class] = @Class
	AND [Teacher] = @Teacher
	AND [Subject] = @Subject
GO

CREATE PROCEDURE [dbo].[pr_tblTaskAssign_DeleteWClassLogic]
	@Class char(10)
AS
DELETE FROM [dbo].[tblTaskAssign]
WHERE
	[Class] = @Class
GO

CREATE PROCEDURE [dbo].[pr_tblTaskAssign_DeleteWTeacherLogic]
	@Teacher char(10)
AS
DELETE FROM [dbo].[tblTaskAssign]
WHERE
	[Teacher] = @Teacher
GO

CREATE PROCEDURE [dbo].[pr_tblTaskAssign_DeleteWSubjectLogic]
	@Subject nvarchar(20)
AS
DELETE FROM [dbo].[tblTaskAssign]
WHERE
	[Subject] = @Subject
GO

CREATE PROCEDURE [dbo].[pr_tblTaskAssign_SelectAll]
AS
SELECT * FROM [dbo].[tblTaskAssign]
ORDER BY 
	[Class] ASC
	, [Teacher] ASC
	, [Subject] ASC
GO

CREATE PROCEDURE [dbo].[pr_tblTaskAssign_SelectAllWClassLogic]
	@Class char(10)
AS
SELECT * FROM [dbo].[tblTaskAssign]
WHERE
	[Class] = @Class
GO

CREATE PROCEDURE [dbo].[pr_tblTaskAssign_SelectAllWTeacherLogic]
	@Teacher char(10)
AS
SELECT * FROM [dbo].[tblTaskAssign]
WHERE
	[Teacher] = @Teacher
GO

CREATE PROCEDURE [dbo].[pr_tblTaskAssign_SelectAllWSubjectLogic]
	@Subject nvarchar(20)
AS
SELECT * FROM [dbo].[tblTaskAssign]
WHERE
	[Subject] = @Subject
GO

CREATE PROCEDURE [dbo].[pr_tblTeacher_Insert]
	@ID char(10),
	@FullName nvarchar(30),
	@Birth datetime,
	@Gender bit,
	@Address nvarchar(50),
	@Email varchar(30),
	@Phone varchar(20),
	@MajorSubject nvarchar(20),
	@Qualification nvarchar(20),
	@EnterDate datetime,
	@ReleaseDate datetime,
	@Status nvarchar(30)
AS
INSERT [dbo].[tblTeacher]
(
	[ID],
	[FullName],
	[Birth],
	[Gender],
	[Address],
	[Email],
	[Phone],
	[MajorSubject],
	[Qualification],
	[EnterDate],
	[ReleaseDate],
	[Status]
)
VALUES
(
	@ID,
	@FullName,
	@Birth,
	@Gender,
	@Address,
	@Email,
	@Phone,
	@MajorSubject,
	@Qualification,
	@EnterDate,
	@ReleaseDate,
	@Status
)
GO

CREATE PROCEDURE [dbo].[pr_tblTeacher_Update]
	@ID char(10),
	@FullName nvarchar(30),
	@Birth datetime,
	@Gender bit,
	@Address nvarchar(50),
	@Email varchar(30),
	@Phone varchar(20),
	@MajorSubject nvarchar(20),
	@Qualification nvarchar(20),
	@EnterDate datetime,
	@ReleaseDate datetime,
	@Status nvarchar(30)
AS
UPDATE [dbo].[tblTeacher]
SET 
	[FullName] = @FullName,
	[Birth] = @Birth,
	[Gender] = @Gender,
	[Address] = @Address,
	[Email] = @Email,
	[Phone] = @Phone,
	[MajorSubject] = @MajorSubject,
	[Qualification] = @Qualification,
	[EnterDate] = @EnterDate,
	[ReleaseDate] = @ReleaseDate,
	[Status] = @Status
WHERE
	[ID] = @ID
GO

CREATE PROCEDURE [dbo].[pr_tblTeacher_Delete]
	@ID char(10)
AS
DELETE FROM [dbo].[tblTeacher]
WHERE
	[ID] = @ID
GO

CREATE PROCEDURE [dbo].[pr_tblTeacher_SelectAll]
AS
SELECT * FROM [dbo].[tblTeacher]
ORDER BY 
	[ID] ASC
GO

CREATE PROCEDURE [dbo].[pr_tblUsers_Insert]
	@UserName varchar(30),
	@Pass char(30),
	@Permission char(10)
AS
INSERT [dbo].[tblUsers]
(
	[UserName],
	[Pass],
	[Permission]
)
VALUES
(
	@UserName,
	@Pass,
	@Permission
)
GO

CREATE PROCEDURE [dbo].[pr_tblUsers_Update]
	@UserName varchar(30),
	@PassOld char(30),
	@PassNew char(30)
AS
UPDATE [dbo].[tblUsers]
SET 
	[Pass] = @PassNew
WHERE
	[UserName] = @UserName
GO

CREATE PROCEDURE [dbo].[pr_tblUsers_Delete]
	@UserName varchar(30)
AS
DELETE FROM [dbo].[tblUsers]
WHERE
	[UserName] = @UserName
GO

CREATE PROCEDURE [dbo].[pr_tblUsers_SelectAll]
	@UserName varchar(30),
	@Pass char(30)
AS
SELECT * FROM [dbo].[tblUsers]
WHERE
	[UserName]=@UserName and
	[Pass]=@Pass
ORDER BY 
	[UserName] ASC
GO

CREATE PROCEDURE [dbo].[pr_tblUsers_Select]
AS
SELECT * FROM [dbo].[tblUsers]
ORDER BY 
	[UserName] ASC
GO

CREATE PROCEDURE [dbo].[pr_tblUsers_SelectOne]
	@UserName varchar(30)
AS
SELECT * FROM [dbo].[tblUsers]
WHERE
	[UserName]=@UserName
ORDER BY 
	[UserName] ASC
GO

CREATE PROCEDURE [dbo].[pr_tblUsers_Search]
	@UserName varchar(30)
AS
SELECT * FROM [dbo].[tblUsers]
WHERE
	[UserName] like '%'+@UserName+'%'
ORDER BY 
	[UserName] ASC
GO

CREATE PROCEDURE [dbo].[pr_tblSession_SelectAll]
	@UserName varchar(30)
AS
SELECT * FROM [dbo].[tblSession]
WHERE
	[UserName]=@UserName and
	[LogoutTime] IS NULL
GO



CREATE PROCEDURE [dbo].[pr_tblSession_Insert]
	@UserName varchar(30)--,
	--@LoginTime char(30)
As
INSERT [dbo].[tblSession]
(
	[UserName],
	[LoginTime]
)
VALUES
(
	@UserName,
	getdate()
)
GO



CREATE PROCEDURE [dbo].[pr_tblSession_Update]
	@UserName varchar(30)
AS
UPDATE [dbo].[tblSession]
SET
	[LogoutTime] = getdate()
WHERE
	[UserName] = @UserName and
	[LogoutTime] is null
GO


ALTER  PROCEDURE [dbo].[pr_tblTeacher_SelectAll]
AS
SELECT *,
Case tblTeacher.Gender when '1' then 'Nam' when '0' then N'N?' END as Sex 
FROM [dbo].[tblTeacher]
ORDER BY 
	[ID] ASC

GO



